package com.course.db;

import java.sql.ResultSet;

public class ChooseCourse extends ExecuteDB {

	//members
	private int Id;
	private int student_id;
	private int course_id;
	private int status;	//0 states waiting for checking, 1 states checked
	private String score;
	private String strSql;
	
	public ChooseCourse()
	{
		this.Id = 0;
		this.student_id = 0;
		this.course_id = 0;
		this.status = 0;
		this.score = "";
	}
	
	public String CountSql()
	{
		this.strSql = "select count(*) from choose_course";
		return this.strSql;
	} 
	
	//add a ChooseCourse
	public boolean add()
	{

		this.strSql="insert into choose_course ";
		this.strSql=this.strSql + "(student_id,course_id,status,score) ";
		this.strSql=this.strSql + "values(" + this.student_id + "," + this.course_id + "," + this.status+ ",'" + this.score + "')";
		System.out.println(this.strSql);
		boolean isAdd = super.exeSql(this.strSql);
        return isAdd;
	}
	
	public int lastid()
	{
		int lid = 0;
		this.strSql = "select last_insert_id() as id from choose_course limit 1";
		ResultSet rs = null;
		try
		{
			rs = super.exeQuery(this.strSql);
			if (rs.next())
				lid = rs.getInt("id");
		}
		catch(Exception e)
		{
			System.out.println(e.toString());
		}
		return lid;
	}
	
	//delete a ChooseCourse by id
    public boolean delete(int ChooseCourseID)
    {
       this.strSql = "delete from choose_course where Id in (";
       this.strSql = this.strSql + ChooseCourseID +")";

       boolean isDelete = super.exeSql(this.strSql);
       return isDelete;
    }
    
  //initialize ChooseCourse information
 	public boolean init()
	{
		this.strSql="select * from `choose_course` where Id=";
		this.strSql=this.strSql + "'" + this.Id + "'";

		try
		{
			ResultSet rs = super.exeQuery(this.strSql);
			if (rs.next())
			{
				this.Id=rs.getInt("Id");
				this.student_id=rs.getInt("student_id");
				this.course_id=rs.getInt("course_id");
				this.status=rs.getInt("status");
				this.score=rs.getString("score");
				return true;
			}
			else
			{
				return false;
			}
		}
		catch(Exception e)
		{
			System.out.println(e.toString());
			return false;
		}
	}
 	
 	//modify ChooseCourse infomation
    public boolean modify_choose_course()
	{
		this.strSql="update choose_course set";
		this.strSql=this.strSql + " student_id=" + "'" + this.student_id + "',";
		this.strSql=this.strSql + " course_id=" + "'" + this.course_id + "',";
		this.strSql=this.strSql + " status=" + this.status + ",";
		this.strSql=this.strSql + " score=" + this.score ;
		this.strSql=this.strSql + " where Id=" + this.Id;
		
		boolean isUpdate = super.exeSql(this.strSql);

		return isUpdate;
	}
    
    public int type1_course_num(int student_id)
    {
    	this.strSql = "select count(*) as num from choose_course as chscs, course_basic as cb where chscs.course_id = cb.Id and type = '知识拓展' and chscs.student_id='"+student_id+"'";
    	int num = 0;
    	ResultSet rs = null;
		try
		{
			rs = super.exeQuery(this.strSql);
			if (rs.next())
			{
				num=rs.getInt("num");
				return num;
			}
			else
			{
				return 0;
			}
		}
		catch(Exception e)
		{
			System.out.println(e.toString());
			num = 0;
		}
		return num;
    }
    
    public int type2_course_num(int student_id)
    {
    	this.strSql = "select count(*) as num from choose_course as chscs, course_basic as cb where chscs.course_id = cb.Id and (type = '学科应用' or type = '大学初级课程') and chscs.student_id='"+student_id+"'";
    	int num = 0;
    	ResultSet rs = null;
		try
		{
			rs = super.exeQuery(this.strSql);
			if (rs.next())
			{
				num=rs.getInt("num");
				return num;
			}
			else
			{
				return 0;
			}
		}
		catch(Exception e)
		{
			System.out.println(e.toString());
			num = 0;
		}
		return num;
    }
    
    public int course_checked(int student_id)
    {
    	this.strSql = "select count(*) as num from choose_course where status='1' and student_id='";
    	this.strSql += student_id + "'";
    	int num = 0;
    	ResultSet rs = null;
		try
		{
			rs = super.exeQuery(this.strSql);
			if (rs.next())
			{
				num=rs.getInt("num");
				return num;
			}
			else
			{
				return 0;
			}
		}
		catch(Exception e)
		{
			System.out.println(e.toString());
			num = 0;
		}
		return num;
    }
    
    public int type3_course_num(int student_id)
    {
    	this.strSql = "select count(*) as num from choose_course as chscs, course_basic as cb where chscs.course_id = cb.Id and (type = '兴趣特长培养' or type = '社会实践活动' or type = '职业技术类') and chscs.student_id='"+student_id+"'";
    	int num = 0;
    	ResultSet rs = null;
		try
		{
			rs = super.exeQuery(this.strSql);
			if (rs.next())
			{
				num=rs.getInt("num");
				return num;
			}
			else
			{
				return 0;
			}
		}
		catch(Exception e)
		{
			System.out.println(e.toString());
			num = 0;
		}
		return num;
    }
 	
	//show ChooseCourse
    public ResultSet show_course_student(int course_id)
	{
		this.strSql="select ch.Id as choose_Id,ch.status as status, ch.course_id as course_id,ch.student_id as student_id,ch.score as score,stu.no as no,stu.name as name,stu.grade as grade  from `choose_course` as ch, student as stu";
		this.strSql=this.strSql + " where ch.student_id = stu.Id and ch.course_id=" + course_id;
		ResultSet rs = null;
		try
		{
			rs = super.exeQuery(this.strSql);
		}
		catch(Exception e)
		{
			System.out.println(e.toString());
		}
		return rs;
	}
    
    
  //show ChooseCourse
    public ResultSet show_grade_course(String grade)
	{
		this.strSql="select st.grade, st.name as stu_name,cb.name as cou_name,teach_location,teach_time from student as st, choose_course as cc, course_basic as cb where st.Id=cc.student_id and cc.course_id=cb.Id and st.grade=";
		this.strSql=this.strSql + "'" + grade+"'";
		ResultSet rs = null;
		try
		{
			rs = super.exeQuery(this.strSql);
		}
		catch(Exception e)
		{
			System.out.println(e.toString());
		}
		return rs;
	}
    
 	//show ChooseCourse
    public ResultSet show_choose_course(int student_id)
	{
		this.strSql="select * from `choose_course` as ch, course_basic as cou";
		this.strSql=this.strSql + " where ch.course_id = cou.Id and ch.student_id=" + student_id;
		ResultSet rs = null;
		try
		{
			rs = super.exeQuery(this.strSql);
		}
		catch(Exception e)
		{
			System.out.println(e.toString());
		}
		return rs;
	}
   
    //initialize ChooseCourse information
 	public boolean getChoosedCourse(int course_id, int student_id)
	{
		this.strSql="select * from `choose_course` where course_id=";
		this.strSql=this.strSql + "'" + course_id + "'";
		this.strSql=this.strSql + " and student_id = '" + student_id + "'";

		try
		{
			ResultSet rs = super.exeQuery(this.strSql);
			if (rs.next())
			{
				this.Id=rs.getInt("Id");
				this.student_id=rs.getInt("student_id");
				this.course_id=rs.getInt("course_id");
				this.status=rs.getInt("status");
				this.score=rs.getString("score");
				return true;
			}
			else
			{
				return false;
			}
		}
		catch(Exception e)
		{
			System.out.println(e.toString());
			return false;
		}
	}
    
  //initialize ChooseCourse information
 	public boolean getCourse(int course_id, int student_id)
	{
 		/*
		this.strSql="select * from `choose_course` where course_id=";
		this.strSql=this.strSql + "'" + course_id + "'";
		this.strSql=this.strSql + " and student_id = '" + student_id + "'";

		try
		{
			ResultSet rs = super.exeQuery(this.strSql);
			if (rs.next())
			{
				this.Id=rs.getInt("Id");
				this.student_id=rs.getInt("student_id");
				this.course_id=rs.getInt("course_id");
				this.status=rs.getInt("status");
				this.score=rs.getString("score");
				return true;
			}
			else
			{
				return false;
			}
		}
		catch(Exception e)
		{
			System.out.println(e.toString());
			return false;
		}*/
 		
 		this.strSql="select * from `choose_course` where course_id=";
		this.strSql=this.strSql + "'" + course_id + "'";
		this.strSql=this.strSql + " and student_id = '" + student_id + "'";
		
		CourseBasic course = new CourseBasic();
		course.setId(course_id);
		course.init();
		int num = 0;
		if(course.getType().equals("知识拓展"))
		{
			num = this.type1_course_num(student_id);
		}
		else if(course.getType().equals("学科应用") || course.getType().equals("大学初级课程"))
		{
			num = this.type2_course_num(student_id);
		}
		else
			num = this.type3_course_num(student_id);
		
		if(num>0)
			return true;
		
		return false;
	}
 	
 	//initialize ChooseCourse information
 	public int course_count(int student_id)
	{
 		//need limit to this term
		this.strSql="select count(*) as num from `choose_course` where student_id=";
		this.strSql=this.strSql + "'" + student_id + "'";
		int num;
		try
		{
			ResultSet rs = super.exeQuery(this.strSql);
			if (rs.next())
			{
				num=rs.getInt("num");
				return num;
			}
			else
			{
				return 0;
			}
		}
		catch(Exception e)
		{
			System.out.println(e.toString());
			return 0;
		}
	}
 	
 	//initialize ChooseCourse information
 	public String check_grade(int course_id)
	{
 		//need limit to this term
		this.strSql="select grade from `course_basic` where Id=";
		this.strSql=this.strSql + "'" + course_id + "'";
		String grade;
		try
		{
			ResultSet rs = super.exeQuery(this.strSql);
			if (rs.next())
			{
				grade=rs.getString("grade");
				return grade;
			}
			else
			{
				return null;
			}
		}
		catch(Exception e)
		{
			System.out.println(e.toString());
			return null;
		}
	}
 	
 	//initialize ChooseCourse information
 	public boolean choose_num_student(int course_id)
	{
 		//need limit to this term
		this.strSql="select num_of_students, students_high from `course_basic` where Id=";
		this.strSql=this.strSql + "'" + course_id + "'";
		try
		{
			ResultSet rs = super.exeQuery(this.strSql);
			if (rs.next())
			{
				return rs.getInt("num_of_students") < rs.getInt("students_high");
			}
			else
			{
				return false;
			}
		}
		catch(Exception e)
		{
			System.out.println(e.toString());
			return false;
		}
	}
 	
 	//initialize ChooseCourse information
 	public Boolean check_choosecourse(int course_id,int status)
	{
 		//need limit to this term
		this.strSql="update choose_course set";
		this.strSql=this.strSql + " status='"+status+"'";
		this.strSql=this.strSql + " where course_id='" + course_id+"'";

		boolean isUpdate = super.exeSql(this.strSql);

		return isUpdate;
	}
 	
	public int getId() {
		return Id;
	}

	public void setId(int id) {
		Id = id;
	}

	public int getStudent_id() {
		return student_id;
	}

	public void setStudent_id(int student_id) {
		this.student_id = student_id;
	}

	public int getCourse_id() {
		return course_id;
	}

	public void setCourse_id(int course_id) {
		this.course_id = course_id;
	}

	public int getStatus() {
		return status;
	}

	public void setStatus(int status) {
		this.status = status;
	}

	public String getScore() {
		return score;
	}

	public void setScore(String score) {
		this.score = score;
	}

	public String getStrSql() {
		return strSql;
	}

	public void setStrSql(String strSql) {
		this.strSql = strSql;
	}

    
}
